package com.lagou.module01.dao.imp;

import com.lagou.module01.bean.PageBean;
import com.lagou.module01.bean.Student;
import com.lagou.module01.bean.User;
import com.lagou.module01.dao.StudentDao;
import com.lagou.module01.utils.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: xyk
 * @Date: Create in 23:17 2021/3/14
 * @Description:
 */
public class StudentDaoImp implements StudentDao {
    @Override
    public List<Student> studentsShow(PageBean pageBean) {
        List<Student> studentList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "select * from student limit ?,?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, pageBean.getStart());
            preparedStatement.setInt(2, pageBean.getRows());
            resultSet = preparedStatement.executeQuery();
            // 4.将查询得到的学员信息存储到list中
            while (resultSet.next()) {
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                String birthday = String.valueOf(resultSet.getDate("birthday"));
                String email = resultSet.getString("email");
                String remark = resultSet.getString("remark");
                Student student = new Student(id, name, sex, birthday, email, remark);
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            try {
                DruidUtils.close(connection, preparedStatement);
                if (null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return studentList;
    }

    @Override
    public int getStudentCount() {
        int studentCount = 0;
        Connection connection = null;
        Statement stat = null;
        ResultSet resultSet = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "select count(*) from student";
            // 3.执行sql语句后获取结果并返回
            stat = connection.createStatement();;
            resultSet = stat.executeQuery(sql);
            // 4.将查询得到的学员总数
            resultSet.next();
            studentCount = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            try {
                DruidUtils.close(connection, stat);
                if (null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return studentCount;
    }

    @Override
    public boolean insertStudent(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultInt = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "insert into student(id, name, sex, birthday, email, remark) values(?, ?, ?, ?, ?, ?)";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getId());
            preparedStatement.setString(2, student.getName());
            preparedStatement.setString(3, student.getSex());
            preparedStatement.setString(4, student.getBirthday());
            preparedStatement.setString(5, student.getEmail());
            preparedStatement.setString(6, student.getRemark());

            resultInt = preparedStatement.executeUpdate();
            // 4.将查询得到的学员信息存储到list中
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtils.close(connection, preparedStatement);
        }
        return resultInt == 1;
    }

    @Override
    public boolean editStudent(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultInt = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "update student set name=?, sex=?, birthday=?, email=?, remark=? where id=?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setString(3, student.getBirthday());
            preparedStatement.setString(4, student.getEmail());
            preparedStatement.setString(5, student.getRemark());
            preparedStatement.setString(6, student.getId());

            resultInt = preparedStatement.executeUpdate();
            // 4.将查询得到的学员信息存储到list中
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtils.close(connection, preparedStatement);
        }
        return resultInt == 1;
    }

    @Override
    public Student queryStudent(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "select * from student where id = ? ";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            resultSet = preparedStatement.executeQuery();

            // 4.获取查询得到的学员
            if (resultSet.next()) {
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                String birthday = String.valueOf(resultSet.getDate("birthday"));
                String email = resultSet.getString("email");
                String remark = resultSet.getString("remark");
                student = new Student(id, name, sex, birthday, email, remark);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            try {
                DruidUtils.close(connection, preparedStatement);
                if (null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return student;
    }

    @Override
    public boolean deleteStudent(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int resultInt = 0;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "delete from student where id=?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            resultInt = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            DruidUtils.close(connection, preparedStatement);
        }
        return resultInt == 1;
    }

    @Override
    public int queryStudentByClassId(String classId) {
        int studentCount = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取数据库连接
            connection = DruidUtils.getConnection();
            // 2.准备sql语句
            String sql = "select count(*) from student where class_id=?";
            // 3.执行sql语句后获取结果并返回
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, classId);
            resultSet = preparedStatement.executeQuery();
            // 4.将查询得到的学员总数
            resultSet.next();
            studentCount = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4.释放相关的资源
            try {
                DruidUtils.close(connection, preparedStatement);
                if (null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return studentCount;
    }

}
